Conversion Functions: CAST and CONVERT in MySQL – Detailed Notes

Course: MySQL Training Course

Topic: Conversion Functions: CAST and CONVERT

Course Link: MySQL Training Course

Demo Link: Register for Course Demo

1. Introduction to Conversion Functions in MySQL

Conversion functions in MySQL are used to convert a value from one data type to another. Data conversion is important when working with numbers stored as text, dates represented as strings, calculations involving different data types, and data imported from external sources.

MySQL provides two important conversion functions: CAST() and CONVERT(). Both functions can convert expressions into specified data types. They are useful for preparing data for calculations, comparisons, sorting, reporting, and database applications.

For example, a table may store the value '250' as a string. If you need to perform a numeric calculation with this value, converting it to an integer can make the intention explicit.

SELECT CAST('250' AS SIGNED) AS converted_value;

Output:

Although MySQL sometimes converts values automatically, using explicit conversion functions makes SQL queries easier to understand and can help avoid unexpected results.

2. Learning Objectives

After completing this topic, you should be able to:

• Understand the purpose of data type conversion in MySQL.
• Explain the difference between implicit and explicit conversion.
• Use the CAST() function to convert values into different data types.
• Use the CONVERT() function for data type and character set conversion.
• Convert strings into integers, decimal numbers, and date values.
• Understand conversion behavior when values contain invalid or unexpected data.
• Apply conversion functions in SELECT queries, calculations, filtering, sorting, and reporting.
• Identify common errors and follow best practices for safe data conversion.

3. What Is Data Type Conversion?

Data type conversion is the process of changing a value from one data type to another. A value can be represented as text, a number, a date, a time, or another supported type.

Consider the following values:

Conversion does not always preserve every detail of the original value. For example, converting a decimal to an integer can remove its fractional portion, while converting a number to a character string changes its representation rather than its mathematical value.

4. Types of Data Conversion

4.1 Implicit Conversion

Implicit conversion occurs when MySQL automatically converts a value to a compatible type while evaluating an expression or comparison.

SELECT '100' + 25 AS total;

MySQL can interpret the numeric string as a number for the arithmetic operation. The result is typically 125.

Implicit conversion can be convenient, but it may produce unexpected results when strings contain nonnumeric characters or when values have incompatible formats.

4.2 Explicit Conversion

Explicit conversion occurs when the SQL query directly specifies the target data type using a function such as CAST() or CONVERT().

SELECT CAST('100' AS SIGNED) + 25 AS total;

Here, the string is explicitly converted into a signed integer before the addition is performed.

4.3 Comparison of Implicit and Explicit Conversion

5. Understanding the CAST() Function

The CAST() function converts an expression into a specified data type. It is commonly used when a query needs to treat a value as an integer, decimal, date, character string, or another supported type.

5.1 Syntax of CAST()

CAST(expression AS data_type)

Explanation:

• expression: The value, column, calculation, or expression that needs conversion.
• AS: Introduces the target data type.
• data_type: The MySQL data type to which the expression should be converted.

5.2 Basic Example

SELECT CAST('500' AS SIGNED) AS number_value;

This query converts the string '500' into a signed integer value.

5.3 Casting a Value to an Integer

Integer conversion is useful when a value represents a whole number but is stored or supplied as text.

SELECT
    CAST('125' AS SIGNED) AS signed_integer,
    CAST('125' AS UNSIGNED) AS unsigned_integer;

SIGNED supports negative and positive integer values within its supported range. UNSIGNED represents nonnegative integer values. Choose the target type based on the expected values and the application requirements.

5.4 Casting a Value to a Decimal

Use a decimal type when the value represents a number that requires fractional precision, such as prices, fees, percentages, or product quantities.

SELECT CAST('125.75' AS DECIMAL(10,2)) AS decimal_value;

In DECIMAL(10,2), the precision is 10 digits in total and the scale is 2 digits after the decimal point.

Always choose sufficient precision and scale for the expected values. Values that exceed the permitted range or scale may be adjusted or produce warnings or errors depending on the context and SQL mode.

5.5 Casting a Value to CHAR

The CHAR target type converts an expression into a character string. This can be useful when preparing values for display or combining values with text.

SELECT CAST(12345 AS CHAR) AS text_value;

The result is the character representation '12345'.

5.6 Casting a String to DATE

MySQL supports conversion to the DATE type for values that represent valid dates.

SELECT CAST('2026-10-16' AS DATE) AS converted_date;

Output:

Use a standard date representation such as YYYY-MM-DD to make date interpretation clear. Invalid or ambiguous date strings require special care because behavior can depend on the input, SQL mode, and conversion context.

5.7 Casting a Value to DATETIME

The DATETIME type represents both a calendar date and a time of day.

SELECT CAST('2026-10-16 14:30:00' AS DATETIME) AS converted_datetime;

This is useful when working with event timestamps, order records, appointment times, and other date-time data.

5.8 Casting NULL

When the expression is NULL, conversion generally returns NULL.

SELECT CAST(NULL AS SIGNED) AS result;

Output:

Conversion functions do not automatically replace missing values. Use functions such as COALESCE() when a specific fallback value is required.

SELECT COALESCE(CAST(NULL AS SIGNED), 0) AS result;

This query returns 0 because COALESCE() selects the first non-NULL expression.

6. Understanding the CONVERT() Function

The CONVERT() function can convert an expression to a specified data type. It can also convert character strings between character sets using its character-set syntax.

6.1 Syntax for Data Type Conversion

CONVERT(expression, data_type)

Example:

SELECT CONVERT('250', SIGNED) AS number_value;

This converts the string '250' to a signed integer.

6.2 Converting a String to an Integer

SELECT CONVERT('750', SIGNED) AS converted_integer;

The result is the numeric value 750.

6.3 Converting a String to a Decimal

SELECT CONVERT('89.95', DECIMAL(8,2)) AS converted_price;

This converts the text representation of a price into a decimal value with two fractional digits.

6.4 Converting a String to DATE

SELECT CONVERT('2026-10-16', DATE) AS converted_date;

This produces a DATE value when the input is valid and compatible with the requested conversion.

6.5 Converting a Number to CHAR

SELECT CONVERT(9876, CHAR) AS converted_text;

This returns the character representation of the numeric value.

6.6 CONVERT() for Character Set Conversion

CONVERT() has another syntax for changing the character set of a string.

CONVERT(expression USING charset_name)

Example:

SELECT CONVERT('MySQL Training' USING utf8mb4) AS converted_text;

This converts the string to the specified character set. The chosen character set must be supported by the MySQL installation. Character set conversion is useful when applications handle multilingual text, imported files, or data exchanged between systems that use different encodings.

Changing a character set is different from changing a value's data type. For example, converting a string to UTF-8 does not turn it into an integer or date.

7. Difference Between CAST() and CONVERT()

CAST() and CONVERT() both support explicit data type conversion, but their syntax differs. CONVERT() additionally provides a character-set conversion form.

Important: For equivalent target types and inputs, CAST() and CONVERT() often produce equivalent results. The functions are not interchangeable in every syntax context because their forms differ, and character-set conversion is a distinct CONVERT() feature.

8. Common Data Types Used with CAST() and CONVERT()

The exact target types supported by CAST() and CONVERT() depend on the MySQL version and syntax. Consult the documentation for the version used in your project, especially when using specialized types or attributes.

9. Creating a Sample Database Table

To understand conversion functions in real-world situations, create a table containing product information. The price and quantity are intentionally stored as text so that conversion can be demonstrated.

CREATE DATABASE conversion_demo;
USE conversion_demo;

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price_text VARCHAR(20),
    quantity_text VARCHAR(20),
    launch_date_text VARCHAR(20)
);

INSERT INTO products
(product_id, product_name, price_text, quantity_text, launch_date_text)
VALUES
(1, 'Keyboard', '799.50', '10', '2026-01-15'),
(2, 'Mouse', '399.99', '25', '2026-02-20'),
(3, 'Monitor', '8999.00', '5', '2026-03-10'),
(4, 'USB Cable', '149.75', '40', '2026-04-05');

The table uses VARCHAR columns for price, quantity, and date text. This is not necessarily the best design for production data, but it is useful for practicing conversion on imported or legacy data.

10. Practical Examples Using CAST()

10.1 Displaying a Text Price as a Decimal

SELECT
    product_name,
    CAST(price_text AS DECIMAL(10,2)) AS price
FROM products;

This query converts each text price into a decimal value for reporting.

10.2 Converting Quantity to an Integer

SELECT
    product_name,
    CAST(quantity_text AS UNSIGNED) AS quantity
FROM products;

This is useful when quantity values are valid nonnegative whole numbers stored as text.

10.3 Calculating Total Inventory Value

SELECT
    product_name,
    CAST(price_text AS DECIMAL(10,2)) AS price,
    CAST(quantity_text AS UNSIGNED) AS quantity,
    CAST(price_text AS DECIMAL(10,2))
        * CAST(quantity_text AS UNSIGNED) AS inventory_value
FROM products;

Both values are converted before multiplication. This makes the intended numeric operation explicit.

10.4 Converting Date Text to DATE

SELECT
    product_name,
    CAST(launch_date_text AS DATE) AS launch_date
FROM products;

When date strings follow the expected format, converting them to DATE makes date-oriented comparisons and date functions easier to use.

10.5 Sorting Numeric Text Correctly

Sorting a VARCHAR column directly generally follows string comparison rules rather than numeric order. For example, text values such as '100', '20', and '3' may not appear in numerical order.

SELECT
    product_name,
    price_text
FROM products
ORDER BY CAST(price_text AS DECIMAL(10,2)) ASC;

This sorts prices numerically after conversion. If a column is frequently used for numeric sorting, storing the data in an appropriate numeric column is usually preferable.

11. Practical Examples Using CONVERT()

11.1 Convert Price Text to Decimal

SELECT
    product_name,
    CONVERT(price_text, DECIMAL(10,2)) AS price
FROM products;

The query performs the same general type of conversion as the equivalent CAST() example.

11.2 Convert Quantity to Integer

SELECT
    product_name,
    CONVERT(quantity_text, UNSIGNED) AS quantity
FROM products;

11.3 Calculate Product Value

SELECT
    product_name,
    CONVERT(price_text, DECIMAL(10,2))
        * CONVERT(quantity_text, UNSIGNED) AS inventory_value
FROM products;

11.4 Convert Date Text

SELECT
    product_name,
    CONVERT(launch_date_text, DATE) AS launch_date
FROM products;

11.5 Convert Text Using a Character Set

SELECT CONVERT('Database Learning' USING utf8mb4) AS message;

This example demonstrates character-set conversion rather than numeric or date conversion.

12. Using Conversion Functions in WHERE Conditions

Conversion functions can be used in a WHERE clause when stored values must be interpreted as another type before filtering.

12.1 Find Products Above a Price

SELECT
    product_name,
    price_text
FROM products
WHERE CAST(price_text AS DECIMAL(10,2)) > 500;

This query returns products whose converted price is greater than 500.

12.2 Find Products with a Minimum Quantity

SELECT
    product_name,
    quantity_text
FROM products
WHERE CONVERT(quantity_text, UNSIGNED) >= 10;

The quantity text is converted to an unsigned integer before the condition is evaluated. This assumes the source values are valid nonnegative integers.

12.3 Filter by a Converted Date

SELECT
    product_name,
    launch_date_text
FROM products
WHERE CAST(launch_date_text AS DATE) >= '2026-03-01';

When the input contains valid date strings, this query compares date values. Invalid strings can produce warnings, errors, or unexpected comparison behavior, so validate source data before relying on the results.

13. Using Conversion Functions in ORDER BY and GROUP BY

13.1 Numeric Sorting

SELECT product_name, price_text
FROM products
ORDER BY CAST(price_text AS DECIMAL(10,2)) DESC;

This returns products from highest to lowest price based on numeric values rather than the text representation.

13.2 Grouping Converted Values

Conversion can also be used when grouping values that are stored in text columns.

SELECT
    CAST(quantity_text AS UNSIGNED) AS quantity,
    COUNT(*) AS product_count
FROM products
GROUP BY CAST(quantity_text AS UNSIGNED)
ORDER BY quantity;

Grouping after conversion treats values that convert to the same numeric value as one group. Make sure the source strings have been validated so that different or malformed inputs do not unexpectedly collapse into the same result.

14. Handling Invalid or Unexpected Values

Conversion functions should not be treated as complete data-validation tools. If a string contains letters, symbols, missing information, or an invalid date, conversion may not behave as expected.

14.1 Invalid Numeric Strings

SELECT CAST('abc' AS SIGNED) AS converted_value;

A nonnumeric string cannot represent a valid integer. Depending on the expression context, MySQL version, and SQL mode, a conversion may produce a value with a warning or raise an error. Do not assume that invalid input will always be rejected automatically.

14.2 Numeric Strings with Extra Characters

SELECT CAST('123abc' AS SIGNED) AS converted_value;

Strings that begin with digits but contain extra characters can be especially dangerous because conversion may interpret a numeric prefix in some contexts. Validate the entire input string instead of assuming that a successful conversion proves the original value was valid.

14.3 Invalid Decimal Strings

SELECT CAST('12.5xyz' AS DECIMAL(10,2)) AS converted_value;

Mixed numeric and nonnumeric input should be checked before conversion. If data is imported from spreadsheets, text files, or external applications, perform validation and cleansing before calculations.

14.4 Invalid Date Strings

SELECT CAST('2026-15-45' AS DATE) AS converted_date;

This string does not represent a valid calendar date. MySQL's response depends on the context and SQL mode, so do not rely on a conversion function alone to verify date validity.

14.5 Checking Warnings

When investigating conversion behavior in a MySQL session, inspect warnings immediately after the statement that may have produced them.

SELECT CAST('abc' AS SIGNED) AS converted_value;
SHOW WARNINGS;

Warnings can help identify truncation, invalid values, and other conversion issues. The exact diagnostic output depends on the operation and MySQL configuration.

15. Safer Data Conversion Practices

15.1 Validate Input Before Conversion

Check whether source values match the expected format before using them in calculations. For example, prices should contain valid numeric values, quantities should contain valid whole numbers, and date fields should follow a known date format.

15.2 Prefer Correct Column Data Types

If a value represents a price, use an appropriate numeric column such as DECIMAL. If it represents a date, use DATE or DATETIME. Conversion functions are useful for processing incoming or legacy data, but they should not be a substitute for a sound database schema.

15.3 Use DECIMAL for Financial Values

For money and other values requiring exact decimal representation, DECIMAL is generally preferable to approximate floating-point types. Choose a precision and scale that match the business requirements.

15.4 Be Careful with NULL Values

NULL means that a value is missing or unknown. Converting NULL normally returns NULL, and calculations involving NULL often return NULL. Use COALESCE() when a fallback value is appropriate, but only when that fallback matches the business meaning.

15.5 Avoid Unnecessary Conversion in Filters

Applying a conversion function to a table column in a WHERE condition can prevent MySQL from using an ordinary index efficiently for that condition. Performance depends on the query, index design, data types, and optimizer behavior.

For frequently filtered numeric or date data, store the values in the correct data type. If a conversion is unavoidable, review the execution plan using EXPLAIN and consider an appropriate generated column or functional index when supported by your MySQL version.

15.6 Be Careful with Signed and Unsigned Values

Signed and unsigned integer types have different supported ranges. Converting negative values to unsigned representations can lead to unexpected results or errors depending on the context. Use UNSIGNED only when negative values are not valid for the data.

16. CAST() and CONVERT() in Real-World Applications

17. Common Mistakes and How to Avoid Them

18. Step-by-Step Practice Workflow

1. Identify the original data type: Determine whether the value is stored as text, an integer, a decimal, or a date.
2. Define the expected result: Decide what type the application or query needs.
3. Inspect the source values: Look for empty strings, unexpected symbols, malformed dates, and NULL values.
4. Select the conversion function: Use CAST() for a clear type conversion or CONVERT() when its syntax is convenient or character-set conversion is needed.
5. Test representative inputs: Include normal values, boundary values, NULL, and malformed values.
6. Review warnings and errors: Check whether the conversion behaves as expected under the current SQL mode.
7. Use the converted value in the query: Apply it to calculations, filtering, sorting, or reporting.
8. Review performance: Use EXPLAIN when conversion appears in a large or frequently executed query.
9. Improve the schema if necessary: Store the data in the correct type when possible rather than converting it repeatedly.

19. Practice Exercises

Exercise 1: Convert a String to an Integer

Write a query that converts the string '450' into a signed integer using CAST().

SELECT CAST('450' AS SIGNED) AS result;

Exercise 2: Convert a String to Decimal

Convert '875.65' to a DECIMAL value with two digits after the decimal point.

SELECT CAST('875.65' AS DECIMAL(10,2)) AS amount;

Exercise 3: Use CONVERT() for Integer Conversion

Convert the string '300' into a signed integer using CONVERT().

SELECT CONVERT('300', SIGNED) AS result;

Exercise 4: Convert Text to DATE

Convert '2026-05-20' into a DATE value.

SELECT CAST('2026-05-20' AS DATE) AS event_date;

Exercise 5: Calculate a Product Total

Given a product price stored as '49.95' and a quantity stored as '8', convert both values and calculate the total.

SELECT
    CAST('49.95' AS DECIMAL(10,2))
    * CAST('8' AS UNSIGNED) AS total;

Exercise 6: Sort Text Prices Numerically

Using the products table created earlier, display product names and prices in ascending numeric order.

SELECT product_name, price_text
FROM products
ORDER BY CAST(price_text AS DECIMAL(10,2)) ASC;

Exercise 7: Convert a Character Set

Use CONVERT() to convert a string to the utf8mb4 character set.

SELECT CONVERT('MySQL Practice' USING utf8mb4) AS message;

20. Mini Project: Cleaning Imported Product Data

Project Objective: Use CAST() and CONVERT() to process product data that has been imported into a staging table with text-based values.

Project Scenario

An online store imports product records from a spreadsheet. The source file contains prices, quantities, and launch dates as strings. The reporting team needs numeric totals, correct price sorting, and date-based filtering.

Tasks

1. Create a staging table containing product names, price strings, quantity strings, and date strings.
2. Insert several valid sample records.
3. Use CAST() to convert price strings to DECIMAL values.
4. Use CONVERT() to convert quantity strings to an integer type.
5. Convert date strings to DATE values.
6. Calculate the total inventory value for each product.
7. Sort the products by numeric price.
8. Test the queries with invalid strings and NULL values.
9. Use SHOW WARNINGS to investigate conversion problems where applicable.
10. Write a short report explaining why validating data and selecting appropriate column types are important.

Example Project Query

SELECT
    product_name,
    CAST(price_text AS DECIMAL(10,2)) AS price,
    CONVERT(quantity_text, UNSIGNED) AS quantity,
    CAST(price_text AS DECIMAL(10,2))
        * CONVERT(quantity_text, UNSIGNED) AS inventory_value,
    CAST(launch_date_text AS DATE) AS launch_date
FROM products
ORDER BY CAST(price_text AS DECIMAL(10,2)) DESC;

This query demonstrates explicit conversion for calculations and display. It assumes the source columns contain valid values. In a production pipeline, validate the data before running calculations and handle invalid records separately.

21. Interview Questions and Answers

Q1. What are conversion functions in MySQL?

Conversion functions change the representation or data type of an expression. CAST() and CONVERT() are commonly used for explicit data type conversion.

Q2. What is the purpose of CAST()?

CAST() converts an expression to a specified data type using the syntax CAST(expression AS data_type).

Q3. What is the syntax of CONVERT() for data type conversion?

The syntax is CONVERT(expression, data_type).

Q4. What is the main difference between CAST() and CONVERT()?

Both support data type conversion, but their syntax differs. CONVERT() also supports character-set conversion using the USING clause.

Q5. How do you convert a string to an integer?

SELECT CAST('100' AS SIGNED);

This converts a numeric string to a signed integer representation.

Q6. How do you convert a string to a decimal?

SELECT CAST('99.95' AS DECIMAL(10,2));

This converts the string to a decimal value with two fractional digits.

Q7. How can you convert a string into a DATE?

SELECT CAST('2026-10-16' AS DATE);

The input should represent a valid date in a suitable format.

Q8. What is implicit conversion?

Implicit conversion occurs when MySQL automatically converts a value to a compatible type while evaluating an expression or comparison.

Q9. What is explicit conversion?

Explicit conversion occurs when the query directly specifies the target type using a function such as CAST() or CONVERT().

Q10. What happens when you convert NULL?

Conversion of NULL generally returns NULL. Use COALESCE() if a meaningful fallback value is required.

Q11. Can conversion functions be used in WHERE clauses?

Yes. For example, WHERE CAST(price_text AS DECIMAL(10,2)) > 500 filters rows based on converted numeric values.

Q12. Why should invalid values be checked before conversion?

Invalid inputs can produce warnings, errors, or unexpected results depending on the conversion and SQL mode. Validation helps maintain data quality.

Q13. Why is DECIMAL commonly used for prices?

DECIMAL provides fixed-point decimal representation, making it suitable for financial values that require controlled precision.

Q14. What is the purpose of CONVERT(value USING utf8mb4)?

It converts a string to the specified character set. It does not convert the string into a numeric or date type.

Q15. Can conversion functions affect query performance?

Yes. Applying a conversion to a column in a filter or join condition may reduce the optimizer's ability to use an ordinary index efficiently. Correct schema design and execution-plan analysis can help.

22. Quick Revision Notes

• Conversion functions change how a value is represented or interpreted.
• CAST() uses the syntax CAST(expression AS data_type).
• CONVERT() uses the syntax CONVERT(expression, data_type) for data type conversion.
• CONVERT() also supports CONVERT(expression USING charset_name) for character-set conversion.
• Use SIGNED or UNSIGNED for appropriate integer conversions.
• Use DECIMAL(M,D) when a fixed-point decimal representation is needed.
• Use DATE and DATETIME for valid date and date-time values.
• Invalid values can produce warnings, errors, or unexpected results.
• NULL generally remains NULL after conversion.
• Validate input before converting it.
• Use the correct data types in database schemas whenever possible.
• Review query performance when conversion functions are applied to large table columns.

23. Best Practices Checklist

• Choose the target data type according to the meaning of the data.
• Use CAST() or CONVERT() when explicit conversion makes a query clearer.
• Validate numeric strings before using them in arithmetic.
• Validate date strings before using them in date comparisons.
• Use DECIMAL for prices and other values that require exact decimal precision.
• Handle NULL and missing values according to the business rules.
• Check warnings during data cleaning and migration tasks.
• Avoid unnecessary repeated conversion in frequently executed queries.
• Prefer correctly typed columns over permanent storage of numbers and dates as text.
• Test conversion logic against normal, boundary, malformed, and missing values.

24. Conclusion

CAST() and CONVERT() are essential MySQL functions for explicit data type conversion. They help transform strings into numbers, represent date values in date types, and prepare data for calculations, filtering, sorting, and reporting. CONVERT() also provides a syntax for character-set conversion.

Although both functions are useful for cleaning imported data and working with legacy databases, conversion should be combined with input validation and appropriate database design. Choosing the correct column types, handling NULL values carefully, inspecting warnings, and considering query performance will help you build more reliable MySQL applications.

To learn more about MySQL and practice database concepts, visit the MySQL Training Course. You can also register for a course demo.